﻿using System;
using System.Collections;
using System.Configuration;
using System.Data;
using KPIS.GERP.GAMS.APP.Reports;
using KPIS.GERP.GAMS.DAL;
using Telerik.Web.UI;
using CrystalDecisions.CrystalReports.Engine;
using CrystalDecisions.Shared;
using System.Web.UI;
using System.Collections.Generic;
using System.IO;
using OfficeOpenXml;
using OfficeOpenXml.Style;
using KPIS.GERP.GAMS.MODEL;
using System.Web;

namespace KPIS.GERP.GAMS.APP.App_UI.Forms
{
    public partial class ACM_Reports : System.Web.UI.Page
    {
        Commons.Main main = new Commons.Main();
        Commons.MailMerge mailMerge = new Commons.MailMerge();
        D_ACM_GENERAL_Ledger d_acm_general_ledger = new D_ACM_GENERAL_Ledger();
        D_ACM_INVENTORY_Control d_acm_inventory_control = new D_ACM_INVENTORY_Control();
        D_ACM_PAYMENT_Gernal d_acm_payment = new D_ACM_PAYMENT_Gernal();
        DateTime dStartDate = DateTime.Today;
        DateTime dEndDate = DateTime.Today;
        string sStartDate = DateTime.Today.ToLongDateString();
        string sEndDate = DateTime.Today.ToLongDateString();

        protected void Page_Load(object sender, EventArgs e)
        {

        }
        protected void CrystalReportViewer1_Init(object sender, EventArgs e)
        {
            LoadReport();
        }
        protected void RadAjaxManager1_AjaxRequest(object sender, AjaxRequestEventArgs e)
        {

        }
        private void LoadReport()
        {
            string strReportName = "";
            ReportDocument rptDoc = new ReportDocument();
            if ((Session["sReportName"].ToString() != "Ledgers") && (Session["sReportName"].ToString() != "TrialBalance") && (Session["sReportName"].ToString() != "TrialBalance_Fiscal")
                && (Session["sReportName"].ToString() != "Inventory") && (Session["sReportName"].ToString() != "Expense") && (Session["sReportName"].ToString() != "Profit_And_Loss")
                && (Session["sReportName"].ToString() != "Balance_Sheet"))
            {
                Session["Exception"] = (Session["Exception"] != null ? Session["Exception"].ToString() : "") + "<br/><br/>" + "ถึงแล้วโว้ย";
                string Start = "";
                string End = "";
                string SourceSeq = "";

                try
                {
                    Session["Exception"] = (Session["Exception"] != null ? Session["Exception"].ToString() : "") + "<br/><br/>" + "ประกาศตัวแปร";
                    rptDoc.Load(Server.MapPath("~/Reports/R_ACM_GeneralJournal.rpt"));
                    //R_ACM_General_Journal report_G_L = new R_ACM_General_Journal();

                    DateTime StartDate = Convert.ToDateTime(Session["dStartDate"]);
                    DateTime EndDate = Convert.ToDateTime(Session["dEndDate"]);
                    string sSourceSeq = Session["sSourceSeq"].ToString();
                    Start = StartDate.Year.ToString() + "-" + StartDate.Month.ToString() + "-" + StartDate.Day.ToString();
                    End = EndDate.Year.ToString() + "-" + EndDate.Month.ToString() + "-" + EndDate.Day.ToString();
                    SourceSeq = sSourceSeq;
                }
                catch (Exception ex)
                {
                    Session["Exception"] = (Session["Exception"] != null ? Session["Exception"].ToString() : "") + "<br/><br/>" + ex.Message;
                }

                
                if (Session["sReportName"].ToString() == "All")
                {
                    DataTable dtb = null;
                    try
                    {
                        Session["Exception"] = (Session["Exception"] != null ? Session["Exception"].ToString() : "") + "<br/><br/>" + "ดึงข้อมูลจากดาต้าเบส";
                        strReportName = "รายงานสมุดรายวัน (ทั้งหมด)";
                        dtb = d_acm_general_ledger.Report_BOOK_General("All", Start, End, SourceSeq);
                    }
                    catch (Exception ex)
                    {
                        Session["Exception"] = (Session["Exception"] != null ? Session["Exception"].ToString() : "") + "<br/><br/>" + ex.Message;
                    }
                    try
                    {
                        Session["Exception"] = (Session["Exception"] != null ? Session["Exception"].ToString() : "") + "<br/><br/>" + "ใส่ดาต้าลงในรีพอร์ต";
                        rptDoc.SetDataSource(dtb);
                    }
                    catch (Exception ex)
                    {
                        Session["Exception"] = (Session["Exception"] != null ? Session["Exception"].ToString() : "") + "<br/><br/>" + ex.Message;
                    }
                }
                else if (Session["sReportName"].ToString() == "General")
                {
                    strReportName = "รายงานสมุดรายวันทั่วไป";
                    rptDoc.SetDataSource(d_acm_general_ledger.Report_BOOK_General("General", Start, End, SourceSeq));
                }
                else if (Session["sReportName"].ToString() == "Receive")
                {
                    strReportName = "รายงานสมุดรายวันรับเงิน";
                    rptDoc.SetDataSource(d_acm_general_ledger.Report_BOOK_General("Receive", Start, End, SourceSeq));
                }
                else if (Session["sReportName"].ToString() == "Payment")
                {
                    strReportName = "รายงานสมุดรายวันจ่ายเงิน";
                    DataTable dtb = d_acm_general_ledger.Report_BOOK_General("Payment", Start, End, SourceSeq);
                    rptDoc.SetDataSource(dtb);
                    //rptDoc.OpenSubreport("R_ACM_PaymentDetails.rpt");

                    //rptDoc.Subreports[0].Database.Tables[0].SetDataSource(d_acm_general_ledger.ReportPaymentDetail());
                }
                else if (Session["sReportName"].ToString() == "Adjust")
                {
                    strReportName = "รายงานสมุดรายปรับปรุงรายการ";
                    rptDoc.SetDataSource(d_acm_general_ledger.Report_BOOK_General("Adjust", Start, End, SourceSeq));
                }

                try
                {
                    // report_G_L.SetDatabaseLogon(main.strUserName, main.strPassword, main.strServerName, main.strDatabaseName);
                    //rptDoc.SetParameterValue("CreateBy", main.strUserID);
                    rptDoc.SetParameterValue("CreateBy", main.strEmpID);
                    rptDoc.SetParameterValue("pTitleName", strReportName);
                    rptDoc.SetParameterValue("pStartDate", (Session["sStartDate"] != null ? Session["sStartDate"].ToString() : ""));
                    rptDoc.SetParameterValue("pEndDate", (Session["sEndDate"] != null ? Session["sEndDate"].ToString() : ""));


                    if (Session["dStartDate"] != null)
                    {
                        rptDoc.SetParameterValue("vStartDate", Session["dStartDate"]);
                    }
                    if (Session["dEndDate"] != null)
                    {
                        rptDoc.SetParameterValue("vEndDate", Session["dEndDate"]);
                    }
                }
                catch (Exception ex4)
                {
                    Session["Exception"] = (Session["Exception"] != null ? Session["Exception"].ToString() : "") + "<br/><br/>" + ex4.Message;
                    throw new Exception(ex4.Message + " on Set ReportSource");
                }

                try
                {
                    rptDoc.ExportToHttpResponse(ExportFormatType.Excel, Response, true, "Journal");
                    //CrystalReportViewer1.ReportSource = rptDoc;
                    //CrystalReportViewer1.ex
                    //CrystalReportViewer1.DataBind();
                }
                catch (Exception ex2)
                {
                    Session["Exception"] = (Session["Exception"] != null ? Session["Exception"].ToString() : "") + "<br/><br/>" + ex2.Message;
                    throw new Exception(ex2.Message + " on load ReportSource to CrystalReportViewer");
                }

            }
            else if (Session["sReportName"].ToString() == "Ledgers")
            {
                Page.Title = "รายงานบัญชีแยกประเภททั่วไป";
                //DateTime StartDate = Convert.ToDateTime(Session["dStartDate"]);
                //DateTime EndDate = Convert.ToDateTime(Session["dEndDate"]);
                //string SourceSeq = Session["sSourceSeq"].ToString();

                //ReportLedger(d_acm_general_ledger.Report_Ledger(StartDate, EndDate, SourceSeq), StartDate, EndDate);

                R_ACM_Ledgers report_ledgers = new R_ACM_Ledgers();
                DateTime StartDate = Convert.ToDateTime(Session["dStartDate"]);
                DateTime EndDate = Convert.ToDateTime(Session["dEndDate"]);
                string AccountSeq = Session["AccountSeq"] != null ? Session["AccountSeq"].ToString() : null;
                string Start = StartDate.Year.ToString() + "-" + StartDate.Month.ToString() + "-" + StartDate.Day.ToString();
                string End = EndDate.Year.ToString() + "-" + EndDate.Month.ToString() + "-" + EndDate.Day.ToString();
                string SourceSeq = Session["sSourceSeq"].ToString();
                DataTable dtb = d_acm_general_ledger.Report_DIVIDE_Type(StartDate, EndDate, SourceSeq, AccountSeq);
                report_ledgers.SetDataSource(dtb);
                // report_ledgers.SetDatabaseLogon(main.strUserName, main.strPassword, main.strServerName, main.strDatabaseName);
                report_ledgers.SetParameterValue("CreateBy", main.strEmpID);
                report_ledgers.SetParameterValue("pStartDate", (Session["sStartDate"] != null ? Session["sStartDate"].ToString() : ""));
                report_ledgers.SetParameterValue("pEndDate", (Session["sEndDate"] != null ? Session["sEndDate"].ToString() : ""));
                if (Session["dStartDate"] != null)
                {
                    report_ledgers.SetParameterValue("vStartDate", Session["dStartDate"]);
                }
                if (Session["dEndDate"] != null)
                {
                    report_ledgers.SetParameterValue("vEndDate", Session["dEndDate"]);
                }

                if ((bool)Session["IsExcel"])
                {
                    report_ledgers.ExportToHttpResponse(ExportFormatType.Excel, Response, true, "Ledger");
                }
                else
                {
                    report_ledgers.ExportToHttpResponse(ExportFormatType.PortableDocFormat, Response, true, "Ledger");
                }
            }
            else if (Session["sReportName"].ToString() == "TrialBalance")
            {
                //Page.Title = "รายงานแสดงงบทดลอง";
                //R_ACM_Trial_Balance reportTrialBalance = new R_ACM_Trial_Balance();
                //DateTime StartDate = Convert.ToDateTime(Session["dStartDate"]);
                //DateTime EndDate = Convert.ToDateTime(Session["dEndDate"]);
                //string Start = StartDate.Year.ToString() + "-" + StartDate.Month.ToString() + "-" + StartDate.Day.ToString();
                //string End = EndDate.Year.ToString() + "-" + EndDate.Month.ToString() + "-" + EndDate.Day.ToString();
                //reportTrialBalance.SetDataSource(d_acm_general_ledger.Report_ACCOUNT_Experiment(Start, End));
                //// reportTrialBalance.SetDatabaseLogon(main.strUserName, main.strPassword, main.strServerName, main.strDatabaseName);
                //reportTrialBalance.SetParameterValue("CreateBy", main.strEmpID);
                //reportTrialBalance.SetParameterValue("pStartDate", (Session["sStartDate"] != null ? Session["sStartDate"].ToString() : ""));
                //reportTrialBalance.SetParameterValue("pEndDate", (Session["sEndDate"] != null ? Session["sEndDate"].ToString() : ""));
                //if (Session["dStartDate"] != null)
                //{
                //    reportTrialBalance.SetParameterValue("vStartDate", Session["dStartDate"]);
                //}
                //if (Session["dEndDate"] != null)
                //{
                //    reportTrialBalance.SetParameterValue("vEndDate", Session["dEndDate"]);
                //}
                //CrystalReportViewer1.DisplayGroupTree = false;
                //CrystalReportViewer1.ReportSource = reportTrialBalance;
                //CrystalReportViewer1.DataBind();
            }
            else if (Session["sReportName"].ToString() == "TrialBalance_Fiscal")
            {
                //Page.Title = "รายงานแสดงงบทดลอง (เรียกดูตามงวดบัญชี)";
                //R_ACM_Trial_Balance_Fiscal reportTrialBalance = new R_ACM_Trial_Balance_Fiscal();
                //reportTrialBalance.SetDataSource(d_acm_general_ledger.Report_ACCOUNT_EXPERIMENT_FISCAL(Session["sFiscalStart"].ToString(), Session["sFiscalEnd"].ToString()));
                //// reportTrialBalance.SetDatabaseLogon(main.strUserName, main.strPassword, main.strServerName, main.strDatabaseName);
                //reportTrialBalance.SetParameterValue("CreateBy", main.strEmpID);
                //reportTrialBalance.SetParameterValue("pStartDate", DateTime.Today.ToShortDateString());
                //reportTrialBalance.SetParameterValue("vStartDate", DateTime.Now);
                //if (Session["dStartDate"] != null)
                //{
                //    reportTrialBalance.SetParameterValue("vStartDate", Session["dStartDate"]);
                //}
                //if (Session["dEndDate"] != null)
                //{
                //    reportTrialBalance.SetParameterValue("vEndDate", Session["dEndDate"]);
                //}

                //CrystalReportViewer1.ReportSource = reportTrialBalance;
                //CrystalReportViewer1.DataBind();
            }
            else if (Session["sReportName"].ToString() == "Balance_Sheet")
            {
                DateTime StartDate = Convert.ToDateTime(Session["dStartDate"]);
                DateTime EndDate = Convert.ToDateTime(Session["dEndDate"]);

                DataTable dt1 = d_acm_general_ledger.GetBalanceSheet1(StartDate, EndDate);
                DataTable dt2 = d_acm_general_ledger.GetBalanceSheet2(StartDate, EndDate);
                DataTable dt3 = d_acm_general_ledger.GetBalanceSheet3(StartDate, EndDate);

                Dictionary<string, DataTable> dtbs = new Dictionary<string, DataTable>();
                dtbs.Add("Asset!A6", dt1);
                dtbs.Add("Debit!A6", dt2);
                dtbs.Add("ShareHolder!A6", dt3);

                string strDate = StartDate.ToShortDateString() + " ถึง " + EndDate.ToShortDateString();
                Dictionary<string, string> selDate = new Dictionary<string, string>();
                selDate.Add("Asset!D4", strDate);
                selDate.Add("Debit!D4", strDate);
                selDate.Add("ShareHolder!D4", strDate);

                mailMerge.MergeExcel(Response, Server.MapPath("~/Reports/Templates/BlanceSheet.xlsx"), string.Format("BlanceSheet{0}", DateTime.Now.ToString("yyyyMMddHHmmssffff")), dtbs, selDate);

                //R_ACM_BALANCE_Sheet reportBalanceSheet = new R_ACM_BALANCE_Sheet();
                //reportBalanceSheet.SetDataSource(d_acm_general_ledger.REPORT_BALANCE_Sheet());
                //reportBalanceSheet.SetParameterValue("pStartDate", (Session["sStartDate"] != null ? Session["sStartDate"].ToString() : ""));
                //reportBalanceSheet.SetParameterValue("pEndDate", (Session["sEndDate"] != null ? Session["sEndDate"].ToString() : ""));

                //if (Session["dStartDate"] != null)
                //{
                //    reportBalanceSheet.SetParameterValue("vStartDate", Session["dStartDate"]);
                //}
                //if (Session["dEndDate"] != null)
                //{
                //    reportBalanceSheet.SetParameterValue("vEndDate", Session["dEndDate"]);
                //}
                //reportBalanceSheet.SetParameterValue("CreateBy", main.strEmpID);
                //CrystalReportViewer1.ReportSource = reportBalanceSheet;
                //CrystalReportViewer1.DataBind();
            }
            else if (Session["sReportName"].ToString() == "Profit_And_Loss")
            {
                R_ACM_PROFIT_AND_Loss reportProfitAndLoss = new R_ACM_PROFIT_AND_Loss();
                reportProfitAndLoss.SetDataSource(d_acm_general_ledger.REPORT_PROFIT_AND_LOSS());
                reportProfitAndLoss.SetParameterValue("pStartDate", (Session["sStartDate"] != null ? Session["sStartDate"].ToString() : ""));
                reportProfitAndLoss.SetParameterValue("pEndDate", (Session["sEndDate"] != null ? Session["sEndDate"].ToString() : ""));
                if (Session["dStartDate"] != null)
                {
                    reportProfitAndLoss.SetParameterValue("vStartDate", Session["dStartDate"]);
                }
                if (Session["dEndDate"] != null)
                {
                    reportProfitAndLoss.SetParameterValue("vEndDate", Session["dEndDate"]);
                }
                reportProfitAndLoss.SetParameterValue("CreateBy", main.strEmpID);
                CrystalReportViewer1.ReportSource = reportProfitAndLoss;
                CrystalReportViewer1.DataBind();
            }
            else if (Session["sReportName"].ToString() == "Inventory")
            {
                R_ACM_INVENTORY_Control reportInventory = new R_ACM_INVENTORY_Control();
                reportInventory.SetDataSource(d_acm_inventory_control.Report_INVENTORY_Control());
                // report.SetDatabaseLogon(main.strUserName, main.strPassword, main.strServerName, main.strDatabaseName);
                reportInventory.SetParameterValue("CreateBy", main.strEmpID);
                CrystalReportViewer1.ReportSource = reportInventory;
                CrystalReportViewer1.DataBind();
            }
            else if (Session["sReportName"].ToString() == "Expense")
            {
                R_ACM_PAYMENT_Gernal reportPayment = new R_ACM_PAYMENT_Gernal();
                reportPayment.SetDataSource(d_acm_payment.Report_PAYMENT_Gernal("55", "A"));
                reportPayment.SetParameterValue("CreateBy", main.strEmpID);

                reportPayment.ExportToHttpResponse(ExportFormatType.PortableDocFormat, Response, true, "Expense.pdf");
            }
        }

        private void ReportLedger(IList<M_ACM_ReportLedger> models, DateTime dateStart, DateTime dateEnd)
        {
            string strTitle = "";
            string strTemplateFilePath = "";
            string strSaveFileName = "";

            FileInfo file = new FileInfo(strTemplateFilePath);

            using (ExcelPackage pck = new ExcelPackage(file, true))
            {
                if (pck.Workbook.Worksheets.Count > 0)
                {
                    ExcelWorksheet ws = pck.Workbook.Worksheets[1];



                    //if (dtbs != null)
                    //{
                    //    foreach (var dtb in dtbs)
                    //    {
                    //        if (dtb.Value.Rows.Count > 0)
                    //        {
                    //            if (dtb.Key.IndexOf('!') >= 0)
                    //            {
                    //                string strSheetName = dtb.Key.Split('!')[0];
                    //                string strCell = dtb.Key.Split('!')[1];

                    //                ws = pck.Workbook.Worksheets[strSheetName];

                    //                if (ws == null)
                    //                {
                    //                    pck.Workbook.Worksheets.Add(strSheetName);
                    //                    ws = pck.Workbook.Worksheets[strSheetName];
                    //                }

                    //                ws.Cells[strCell].LoadFromDataTable(dtb.Value, false, tableStyle);
                    //            }
                    //            else
                    //            {
                    //                ws.Cells[dtb.Key].LoadFromDataTable(dtb.Value, false, tableStyle);
                    //            }
                    //        }
                    //    }
                    //}

                    //if (Dics != null)
                    //{
                    //    foreach (var Dic in Dics)
                    //    {
                    //        if (Dic.Key.IndexOf('!') >= 0)
                    //        {
                    //            string strSheetName = Dic.Key.Split('!')[0];
                    //            string strCell = Dic.Key.Split('!')[1];

                    //            var ws2 = pck.Workbook.Worksheets[strSheetName];

                    //            if (ws2 == null)
                    //            {
                    //                pck.Workbook.Worksheets.Add(strSheetName);
                    //                ws2 = pck.Workbook.Worksheets[strSheetName];
                    //            }

                    //            ws2.Cells[strCell].Value = Dic.Value;
                    //        }
                    //        else
                    //        {
                    //            ws.Cells[Dic.Key].Value = Dic.Value;
                    //        }
                    //    }
                    //}

                    //if (mergeFieldList != null)
                    //{
                    //    foreach (var mf in mergeFieldList)
                    //    {
                    //        if (mf.IndexOf('!') >= 0)
                    //        {
                    //            string strSheetName = mf.Split('!')[0];
                    //            string strCell = mf.Split('!')[1];

                    //            var ws2 = pck.Workbook.Worksheets[strSheetName];

                    //            if (ws2 == null)
                    //            {
                    //                pck.Workbook.Worksheets.Add(strSheetName);
                    //                ws2 = pck.Workbook.Worksheets[strSheetName];
                    //            }

                    //            ws2.Cells[mf].Merge = true;
                    //        }
                    //        else
                    //        {
                    //            ws.Cells[mf].Merge = true;
                    //        }
                    //    }
                    //}

                    Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
                    Response.AddHeader("content-disposition", string.Format("attachment;  filename={0}.xlsx", HttpUtility.UrlEncode(strSaveFileName, System.Text.Encoding.UTF8)));
                    Response.BinaryWrite(pck.GetAsByteArray());
                    pck.Dispose();
                }
            }

        }
    }
}
